package VerticalPrototypes;

import java.sql.*;

/**
 * @author Tyler
 */

//Class for lending an item to another user; item switches inventory and status.
public class LendItem {
    
    //Instance of this class being used.
    private static LendItem instance = null;
    
    public LendItem()
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        //Hardcoded database location.
        String url = "snyamathi.dyndns.org:37965";
        String user = "root";
        String password = "baconatmidnight";
        
        //Hardcoded prototype userIDs and itemID.
        int user_id1 = 1; //Owner
        int user_id2 = 2; //Borrower
        int item_id = 1; //Item ID
        int new_status = 201; //Item status code when lent.
        
        
        try {
            //Connect to the databse.
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();

            //Lend an item from one user to another.
  
            //Make sure item can be lent out.
				//Tyler's manual test string: SELECT * FROM userinventory WHERE itemID = 1 AND userID = 1 AND status = 101;
            if(rs == (st.executeQuery("SELECT * FROM userinventory WHERE itemID = " + item_id + " AND userID = " + user_id1 + " AND status = 101")))
            {   //Check if the item is not already owned/in the inventory of the borrowing user.
					//Tyler's manual test string: SELECT * FROM userinventory WHERE itemID = 1 AND userID = 2;
                if(!(rs == st.executeQuery("SELECT * FROM userinventory WHERE itemID = " + item_id + " AND userID = " + user_id2)))
                {
                    //Update item status from User1 to user2
						//Tyler's manual test string: UPDATE userinventory SET status= 102 WHERE itemID = 1 AND userID = 1;
                    if(rs == st.executeQuery("UPDATE userinventory SET status=102 WHERE itemID = " + item_id + " AND userID = " + user_id1))
                    {
						//Tyler's manual test string: INSERT INTO userinventory (itemID, userID, status, ownerID) VALUES (1, 2, 201, 1);
                        rs = st.executeQuery("INSERT INTO userinventory (itemID, userID, status, ownerID) VALUES (" + item_id + ", " + user_id2 + ", " + new_status + "," + user_id1 + ")");
                    }
                }
            }
            else
            {
                //Can't lend out a book from owner to borrow.
            }

            if (rs.next()) {
                System.out.println(rs.getString(1));
            }

        } catch (SQLException ex) {
                //Something happens.
        } finally {
            //Close connections safely - May not need to do this? Overkill?
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                    //Something happens.
            }
        }
    }
    
    //Execute the lending of an item to another user.
    public static LendItem user_LendItem()
    {
        if(instance == null)
            instance = new LendItem();
        return instance;
    }  
}